﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Text;

namespace LS.FrmUI.Common
{
   public class OfficeUtils
    {
       public static DataSet ExcelToDataSetByGjp(string strFileSourse)
       {
           DataSet ds = new DataSet();
           //Excel数据源（兼容03/10）  
           string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strFileSourse + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"";

           //连接  
           OleDbConnection conn = new OleDbConnection(strConn);
           try
           {
               conn.Open();

               StringBuilder strsql = new StringBuilder();

               if (strFileSourse.Contains("维修工单.xlsx"))
               {
                   strsql.Append("  select  维修工单 as [KITID] ,送修人 as [CName],联系电话 as [CPhone] ,型号 as [PType],产品条码 as [ISN],受理时间 as [DateTime],实收金额 as [Money],体专店名称 as [JName] ,经销商名称 as [JNameError],1 as sort from  [Sheet1$] ");
               }

               if (strFileSourse.Contains("检测工单明细.xlsx"))
               {
                   strsql.Append("  select  检测单号 as [KITID] ,送修人 as [CName],联系电话 as [CPhone] ,手机型号 as [PType],手机IMEI as [ISN],上报日期 as [DateTime],实收总金额 as [Money],体专店名称 as [JName] ,经销商名称 as [JNameError],2 as sort  from  [Sheet1$] ");
               }

               if (strFileSourse.Contains("附件销售明细导出.xlsx"))
               {
                   strsql.Append("  select  销售单号 as [KITID] ,'' as [CName],手机号码 as [CPhone] ,附件名称 as [PType],'' as [ISN],创建时间 as [DateTime],实收金额 as [Money],用户姓名 as [JName] ,'' as [JNameError],3 as sort from  [Sheet1$] ");
               }
               OleDbDataAdapter OleDa = new OleDbDataAdapter(strsql.ToString(), conn);
               OleDa.Fill(ds, "data_importRecord");
               conn.Close();
           }
           catch (Exception ex)
           {
               throw new Exception("[ErrorCode:ReadError]读取Excel错误，详情:"+ex.Message);
           }
           finally
           {
               if (conn.State != ConnectionState.Closed)
               {
                   conn.Close();
               }
           }
           return ds;
       }

    }
}
